"""
    Author:
    Date: 2019-01-21

    通话管理类
"""
from apps.gma import helpers as gma_helpers
from entities.consts import *
from utils import db
from utils import util


def get_call_count(begin_time, end_time, request=None):
    """ 获取用户在指定时间内的通话次数 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      IFNULL(COUNT(*), 0) AS call_count
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.callee_uid
        WHERE       {apps_condition} AND
                    c.begin_time>=%(begin_time)s AND
                    c.begin_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['call_count'] if data else 0


def get_watcher_call_count(begin_time, end_time):
    """ 获取观众在指定时间内的通话次数 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS call_count
        FROM        cmf_vchat_call_log
        WHERE       begin_time>=%(begin_time)s AND
                    begin_time<=%(end_time)s AND
                    caller_uid=consumer_uid;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['call_count'] if data else 0


def get_caster_call_count(begin_time, end_time):
    """ 获取主播在指定时间内的通话次数 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS call_count
        FROM        cmf_vchat_call_log
        WHERE       begin_time>=%(begin_time)s AND
                    begin_time<=%(end_time)s AND
                    caller_uid!=consumer_uid;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['call_count'] if data else 0


def get_call_records(begin_time, end_time, request=None):
    """ 获取指定时间段内的通话记录 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.callee_uid
        WHERE       {apps_condition} AND
                    c.end_time>=%(begin_time)s AND
                    c.begin_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def get_call_seconds(begin_time, end_time, request=None):
    """ 获取指定时间段内的通话时长 """
    records = get_call_records(begin_time, end_time, request=request)

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in records:
        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['chat_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_valid_call_seconds(begin_time, end_time, request=None):
    """ 获取指定时间段内的有效通话时长 """
    records = get_call_records(begin_time, end_time, request=request)

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in records:
        if record['expense'] == 0:
            continue

        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['chat_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_total_total_call_count():
    """ 获取累积的通话次数 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS total_count
        FROM        cmf_vchat_call_log;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def get_total_total_call_seconds():
    """ 获取累积的通话时长 """
    sql = '''
        SELECT      IFNULL(SUM(chat_seconds), 0) AS total_seconds
        FROM        cmf_vchat_call_log;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_seconds'] if data else 0


def get_broadcaster_call_record(call_id):
    """ 获取主播的通话记录 """
    sql = '''
        SELECT      c.*,
                    u.avatar AS caller_avatar,
                    u2.avatar AS callee_avatar
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.caller_uid
        JOIN        cmf_users AS u2 ON u2.id=c.callee_uid
        WHERE       c.id=%(call_id)s;
    '''
    params = {
        'call_id': str(call_id),
    }
    data = db.query_data_from_db(sql, params)
    return data[0] if data else None


# def get_chat_log_url(call_id):
#     """ 获取视频聊天的录屏日志url """
#     sql = '''
#         SELECT      *
#         FROM        cmf_vchat_call_log
#         WHERE       id=%(call_id)s;
#     '''
#     params = {
#         'call_id': call_id,
#     }
#     data = db.query_data_from_db(sql, params)
#     if data is None:
#         return ''
#
#     return data[0]['chatlog_url']

def get_call_count_group_by_day(count):
    """ 获取每天的通话记录 """
    sql = f'''
        SELECT      date_format(begin_time, '%Y-%m-%d') AS call_date,
                    count(*) call_count
        FROM        cmf_vchat_call_log
        WHERE       chat_seconds > 0 AND
                    review=0 AND
                    (status={CALL_STATUS_END} or status={CALL_STATUS_SHUTDOWN})
        GROUP BY    call_date
        ORDER BY    call_date DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_call_log_between(begin_time, end_time):
    """ 获取在指定时间段内通话记录 """
    sql = f'''
        SELECT      *,
                    c.id AS cid,
                    u.avatar AS caller_avatar,
                    u2.avatar AS callee_avatar
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.caller_uid
        JOIN        cmf_users AS u2 ON u2.id=c.callee_uid
        WHERE       c.begin_time>=%(begin_time)s AND
                    c.begin_time<=%(end_time)s AND
                    c.chat_seconds > 0 AND
                    c.review=0 AND
                    u.is_test_account=0 AND
                    u2.is_test_account=0 AND
                    (c.status={CALL_STATUS_END} OR c.status={CALL_STATUS_SHUTDOWN})
        ORDER BY    c.id DESC;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    pos = len(data)
    for msg in data:
        msg['pos'] = pos
        pos -= 1
    return data if data else []


def set_call_log_review_status(cid, review):
    """ 更新主播的认证状态 """
    sql = f'''
        UPDATE      cmf_vchat_call_log
        SET         review={review}
        WHERE       id={cid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_daily_call_count(day, request=None):
    """ 获取每日的通话次数统计数据 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request, 'u2.origin_bundle_id')
    sql = f'''
        SELECT      DATE_FORMAT(add_time, '%Y-%m-%d') AS d,
                    COUNT(*) as total_count,
                    SUM(chat_seconds) AS total_seconds
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u1 ON u1.id=c.caller_uid
        JOIN        cmf_users AS u2 ON u2.id=c.callee_uid
        WHERE       {apps_condition} AND
                    u1.is_test_account=0 AND
                    u2.is_test_account=0
        GROUP BY    d
        ORDER BY    d DESC
        LIMIT       {day};
    '''
    # c.status in ({CALL_STATUS_END}, {CALL_STATUS_SHUTDOWN}) AND
    data = db.query_data_from_db(sql)
    return data if data else []


if __name__ == '__main__':
    print('ok')

